USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetContacts')
	BEGIN
		DROP  Procedure  dbo.GetContacts
	END

GO

CREATE Procedure dbo.GetContacts
	@isActive		VARCHAR(10) = NULL
,	@userId			VARCHAR(10) = '0'
AS
BEGIN
	
	IF(@isActive IS NOT NULL)
		SET @ISACTIVE = CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '0' END

	;WITH A AS(
		-- CONTACT ENTRIES IN TR_PARTNER
		SELECT
			p.PartnerNo				as [ContactNo]
		,	p.PartnerLastName		as [LastName]
		,	p.PartnerFirstName		as [FirstName]
		,	p.PartnerGUID			as [PKID]
		FROM dbo.TR_Partner as p with (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = p.PartnerGUID 
		WHERE	
			p.PartnerCategory = 1	-- PERSON
		AND p.PartnerType = 3		-- CONTACT
		AND	r.RelationshipType = 3	-- ACCOUNT
		AND r.Partner2 = ISNULL(@userID, r.Partner2)
		AND p.ISActive = ISNULL(@isActive, p.IsActive)
	), B AS (
		SELECT
			p.PartnerName			as [AccountName]
		,	A.PKID
		FROM TR_PARTNER As p WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.Partner2 = p.PartnerNo
		INNER JOIN A
			ON A.PKID = r.SourceGUID
		WHERE
			r.RelationshipType = 8
		AND	p.PartnerCategory = 2 -- ORGANIZATION
		AND p.PartnerType = 2 -- CHILD ACCOUNT
	), C AS (
		-- CONTACT TABLE
		SELECT
			con.JobTitle
		,	A.PKID
		FROM TR_CONTACT as con WITH (NOLOCK)
		INNER JOIN A
			ON A.PKID = con.ContactGUID
	), D AS (
		-- COMMUNICATION
		SELECT
			com.Phone 
		,	com.CellPhone
		,	A.PKID
		FROM TR_COMMUNICATION as com WITH (NOLOCK)
		INNER JOIN A
			ON A.PKID = com.PartnerGUID
	), FINAL AS (
		SELECT 
			A.ContactNo				AS [Contact No]
		,	A.LastName				As [Last Name]
		,	A.FirstName				AS [First Name]
		,	B.AccountName			as [Account Name]
		,	C.JobTitle				AS [Job Title]
		,	D.Phone					AS [Business Phone]
		,	D.CellPhone				As [Mobile Phone]
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			ON B.PKID = A.PKID
		LEFT OUTER JOIN C
			ON C.PKID = A.PKID
		LEFT OUTER JOIN D
			ON D.PKID = A.PKID
	)


	SELECT * FROM FINAL ORDER BY [Last Name], [First Name]
END
GO


-- GetContacts @USERID = NULL